package com.chart.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.chart.dao.ChartDao;
import com.domain.vo.MoneyVo;
import com.util.JdbcUtil;

public class ChartDaoImpl implements ChartDao {

	@Override
	public List<MoneyVo> findPartNameAndMoney() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<MoneyVo> list = new ArrayList<>();
		try {
			conn = JdbcUtil.getConnection();
			String sql  = " select part_name , (sell_price - buy_price)*total "
					+ "from parts inner join (	select part_id  , sum(part_count) "
					+ "total from order_details  group by part_id)p on parts.part_id "
					+ " = p.part_id";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				MoneyVo mv = new MoneyVo();
				mv.setName( rs.getString(1) );
				mv.setMoney( rs.getDouble(2)  );
				list.add(mv);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(ps, rs);
		}
		return list;
	}

}
